//
//  NoSpecFmdbTool.m
//  HAODE
//
//  Created by LiYinDi on 16/5/14.
//  Copyright © 2016年 reabam.com. All rights reserved.
//

#import "NoSpecFmdbTool.h"
#define kUserDBNAME @"userDB.sqlite"
@implementation NoSpecFmdbTool
static FMDatabase *_fmdb;
+(void)initialize
{
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *DBPath = [documentPath stringByAppendingPathComponent:kUserDBNAME];
    _fmdb = [FMDatabase databaseWithPath:DBPath];
    if ([_fmdb open]) {
        
        BOOL result=[_fmdb executeUpdate:@"CREATE TABLE IF NOT EXISTS t_NoSpec (id integer PRIMARY KEY AUTOINCREMENT , userToken text NOT NULL , itemId text NOT NULL , itemCode text NOT NULL , imageUrlFull text NOT NULL , itemName text NOT NULL , maxPrice text NOT NULL , minPrice text NOT NULL , salePrice text NOT NULL , invQty text NOT NULL , specType text NOT NULL , specCount text NOT NULL , typeString text NOT NULL)"];
        if (result) {
            
        }
        else
        {
            
        }
    }

}
+ (BOOL)insert:(NoSpecFmdbModel *)model
{
    
       return [_fmdb executeUpdate:@"INSERT INTO t_NoSpec (userToken,itemId,itemCode, imageUrlFull,itemName,maxPrice,minPrice,salePrice,invQty,specType,specCount,typeString) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",model.userToken,model.itemId,model.itemCode,model.imageUrlFull,model.itemName,model.maxPrice,model.minPrice,model.salePrice,model.invQty,model.specType,model.specCount,model.typeString];
 }
+ (BOOL)modifySpecCount:(NoSpecFmdbModel *)model
{
    NSString *queryStr=[NSString stringWithFormat:@"SELECT * from t_NoSpec WHERE userToken = '%@' AND itemId = '%@' AND typeString = '%@' ;",model.userToken,model.itemId,model.typeString];
    FMResultSet *resultSet = [_fmdb executeQuery:queryStr];
    if([resultSet next])
    {
        NSInteger idINDEX = (NSInteger)[resultSet objectForColumnName:@"id"];
       BOOL res= [_fmdb executeUpdate:@"UPDATE t_NoSpec SET specCount = ?  WHERE id = ?;",model.specCount,idINDEX];
        [resultSet close];
        return res;
    }
    else
    {
    [resultSet close];
      return [self insert:model];
    }
   
}
+ (BOOL)deleteData:(NSString *)deleteSql {
    
    if (deleteSql.length == 0) {
        deleteSql = @"DELETE FROM t_NoSpec";
    }
    
    return [_fmdb executeUpdate:deleteSql];
    
}
+ (NSArray *)queryLeftJoin:(NSString *)typeStr userToken:(NSString *)userToken
{
    NSString *querySql=[NSString stringWithFormat:@"SELECT mainTable.itemID ,mainTable.itemCode,mainTable.itemName,mainTable.salePrice,mainTable.typeString ,mainTable.specCount AS mainCount ,mainTable.imageUrlFull , subTable.specPrice,IFNULL(subTable.specName,'') AS specName ,subTable.specCount ,subTable.specID FROM t_NoSpec mainTable LEFT JOIN t_Spec subTable ON mainTable.itemId = subTable.itemID AND mainTable.typeString = subTable.typeString WHERE mainTable.typeString = '%@' AND mainTable.userToken = '%@' ;",typeStr,userToken];
    NSMutableArray *dataArray=[NSMutableArray array];
    FMResultSet *resultSet = [_fmdb executeQuery:querySql];
    while ([resultSet next]) {
        
        NSString *itemIDString=[resultSet objectForColumnName:@"itemID"];
        NSString *itemCodeString=[resultSet objectForColumnName:@"itemCode"];
        NSString *imageUrlFullString=[resultSet objectForColumnName:@"imageUrlFull"];
        NSString *itemNameString=[resultSet objectForColumnName:@"itemName"];
        NSString *salePriceString=[resultSet objectForColumnName:@"salePrice"];
        //      NSString *typeStringString=[resultSet objectForColumnName:@"typeString"];
        NSString *mainCountString=[resultSet objectForColumnName:@"mainCount"];
        
        NSString *specPriceString=[resultSet objectForColumnName:@"specPrice"];
        NSString *specNameString=[resultSet objectForColumnName:@"specName"];
        NSString *specCountString=[resultSet objectForColumnName:@"specCount"];
        NSString *specIDString=[resultSet objectForColumnName:@"specID"];
        
        LeftJoinFmdbModel *modal = [LeftJoinFmdbModel modalWith:itemIDString itemCode:itemCodeString imageUrlFull:imageUrlFullString itemName:itemNameString salePrice:salePriceString mainCount:mainCountString specPrice:specPriceString specName:specNameString specCount:specCountString specID:specIDString];
        [dataArray addObject:modal];
    }
    [resultSet close];
    return dataArray;

}
+ (NSArray *)queryData:(NSString *)querySql
{
    if (querySql.length == 0) {
        querySql = @"SELECT * FROM t_NoSpec;";
    }
    NSMutableArray *dataArray=[NSMutableArray array];
    FMResultSet *resultSet = [_fmdb executeQuery:querySql];
    while ([resultSet next]) {
        
        NSString *userToken = [resultSet stringForColumn:@"userToken"];
        NSString *itemId = [resultSet stringForColumn:@"itemId"];
        NSString *itemCode = [resultSet stringForColumn:@"itemCode"];
        NSString *imageUrlFull = [resultSet stringForColumn:@"imageUrlFull"];
        NSString *itemName = [resultSet stringForColumn:@"itemName"];
        NSString *maxPrice = [resultSet stringForColumn:@"maxPrice"];
        NSString *minPrice = [resultSet stringForColumn:@"minPrice"];
        NSString *salePrice = [resultSet stringForColumn:@"salePrice"];
        NSString *specType = [resultSet stringForColumn:@"specType"];
        NSString *invQty = [resultSet stringForColumn:@"invQty"];
        NSString *specCount = [resultSet stringForColumn:@"specCount"];
        NSString *typeString = [resultSet stringForColumn:@"typeString"];
        
        NoSpecFmdbModel *modal = [NoSpecFmdbModel modalWith:userToken itemId:itemId itemCode:itemCode imageUrlFull:imageUrlFull itemName:itemName maxPrice:maxPrice minPrice:minPrice salePrice:salePrice invQty:invQty specType:specType specCount:specCount typeString:typeString];
        [dataArray addObject:modal];
    }
    [resultSet close];
    return dataArray;
}
+(NSString *)getSubTableCellCount:(NoSpecFmdbModel *)model
{
    NSString *resultStr=@"";
    NSString *sqlStr=[NSString stringWithFormat:@"SELECT IFNULL(specCount,'') as count11 FROM t_NoSpec WHERE userToken = '%@' AND itemId = '%@' AND typeString = '%@';" ,model.userToken,model.itemId,model.typeString];
    
     FMResultSet *resultSet=[_fmdb executeQuery:sqlStr];
    while (resultSet.next) {
        NSString *countStr=[resultSet objectForColumnName:@"count11"];
        
        resultStr=countStr;

    }
    [resultSet close];
    return resultStr;
}
+(NSString *)getTotalNoSpecMoney:(NoSpecFmdbModel *)model
{
    NSString *resultStr=@"";
    NSString *sqlStr=[NSString stringWithFormat:@"SELECT IFNULL(sum(specCount*salePrice),'') as total11 FROM t_NoSpec WHERE userToken = '%@' AND typeString = '%@' AND specType = '%@';" ,model.userToken,model.typeString ,@"0"];
    FMResultSet *resultSet=[_fmdb executeQuery:sqlStr];
    while (resultSet.next) {
        NSString *TOTAL11=[resultSet objectForColumnName:@"total11"];
        
        resultStr=TOTAL11;
        
    }
    [resultSet close];
    return resultStr;
}

+(NSString *)getCornerCount:(NoSpecFmdbModel *)model
{
    NSString *resultStr=@"";
    NSString *sqlStr=[NSString stringWithFormat:@"SELECT IFNULL(sum(specCount),'') as cornerTotal FROM t_NoSpec WHERE userToken = '%@' AND typeString = '%@';" ,model.userToken,model.typeString];
    FMResultSet *resultSet=[_fmdb executeQuery:sqlStr];
    while (resultSet.next) {
        NSString *TOTAL11=[resultSet objectForColumnName:@"cornerTotal"];
//        NSLog(@"TOTAL11====%@",TOTAL11);
        resultStr=TOTAL11;
        
    }
    [resultSet close];
    return resultStr;
}

//+ (BOOL)deleteData:(NSString *)deleteSql
@end
